﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace Bus_proj
{
    public class Select
    {

        public int GetId()
        {
            int id;
            string sqltext = @"SELECT ISNULL (MAX(Passenger_id),0)
                                from Table_Passenger";
            SqlCommand sqlcomm = new SqlCommand();
            SqlConnection sqlconn = new SqlConnection("Server=MONSTERTUA;Database=Bus_pro;User Id=sa1;Password=qwerty123;");
            sqlcomm.Connection = sqlconn;
            sqlcomm.CommandType = System.Data.CommandType.Text;
            sqlcomm.CommandText = sqltext;
            try
            {
                sqlcomm.Connection.Open();
                id = (int)sqlcomm.ExecuteScalar() + 1;
            }
            catch (Exception)
            {

                sqlcomm.Connection.Close();
                sqlcomm.Connection.Dispose();
                throw;
            }
            sqlcomm.Connection.Close();
            sqlcomm.Connection.Dispose();
            return id;
        }

        public int GetId_emp()
        {
            int id;
            string sqltext = @"SELECT ISNULL (MAX(Personnel_id),0)
                                from Table_Personnel";
            SqlCommand sqlcomm = new SqlCommand();
            SqlConnection sqlconn = new SqlConnection("Server=MONSTERTUA;Database=Bus_pro;User Id=sa1;Password=qwerty123;");
            sqlcomm.Connection = sqlconn;
            sqlcomm.CommandType = System.Data.CommandType.Text;
            sqlcomm.CommandText = sqltext;
            try
            {
                sqlcomm.Connection.Open();
                id = (int)sqlcomm.ExecuteScalar() + 1;
            }
            catch (Exception)
            {

                sqlcomm.Connection.Close();
                sqlcomm.Connection.Dispose();
                throw;
            }
            sqlcomm.Connection.Close();
            sqlcomm.Connection.Dispose();
            return id;
        }

        public int GetId_Route()
        {
            int id;
            string sqltext = @"SELECT ISNULL (MAX(Route_id),0)
                                from Table_Route";
            SqlCommand sqlcomm = new SqlCommand();
            SqlConnection sqlconn = new SqlConnection("Server=MONSTERTUA;Database=Bus_pro;User Id=sa1;Password=qwerty123;");
            sqlcomm.Connection = sqlconn;
            sqlcomm.CommandType = System.Data.CommandType.Text;
            sqlcomm.CommandText = sqltext;
            try
            {
                sqlcomm.Connection.Open();
                id = (int)sqlcomm.ExecuteScalar() + 1;
            }
            catch (Exception)
            {

                sqlcomm.Connection.Close();
                sqlcomm.Connection.Dispose();
                throw;
            }
            sqlcomm.Connection.Close();
            sqlcomm.Connection.Dispose();
            return id;
        }

        public int GetId_point()
        {
            int id;
            string sqltext = @"SELECT ISNULL (MAX(Point_id),0)
                                from Table_Point";
            SqlCommand sqlcomm = new SqlCommand();
            SqlConnection sqlconn = new SqlConnection("Server=MONSTERTUA;Database=Bus_pro;User Id=sa1;Password=qwerty123;");
            sqlcomm.Connection = sqlconn;
            sqlcomm.CommandType = System.Data.CommandType.Text;
            sqlcomm.CommandText = sqltext;
            try
            {
                sqlcomm.Connection.Open();
                id = (int)sqlcomm.ExecuteScalar() + 1;
            }
            catch (Exception)
            {

                sqlcomm.Connection.Close();
                sqlcomm.Connection.Dispose();
                throw;
            }
            sqlcomm.Connection.Close();
            sqlcomm.Connection.Dispose();
            return id;
        }

        public int GetId_time()
        {
            int id;
            string sqltext = @"SELECT ISNULL (MAX(Time_id),0)
                                from Table_Time";
            SqlCommand sqlcomm = new SqlCommand();
            SqlConnection sqlconn = new SqlConnection("Server=MONSTERTUA;Database=Bus_pro;User Id=sa1;Password=qwerty123;");
            sqlcomm.Connection = sqlconn;
            sqlcomm.CommandType = System.Data.CommandType.Text;
            sqlcomm.CommandText = sqltext;
            try
            {
                sqlcomm.Connection.Open();
                id = (int)sqlcomm.ExecuteScalar() + 1;
            }
            catch (Exception)
            {

                sqlcomm.Connection.Close();
                sqlcomm.Connection.Dispose();
                throw;
            }
            sqlcomm.Connection.Close();
            sqlcomm.Connection.Dispose();
            return id;
        }

        public int GetId_Bus_Line()
        {
            int id;
            string sqltext = @"SELECT ISNULL (MAX(Bus_Line_id),0)
                                from Table_Bus_Line";
            SqlCommand sqlcomm = new SqlCommand();
            SqlConnection sqlconn = new SqlConnection("Server=MONSTERTUA;Database=Bus_pro;User Id=sa1;Password=qwerty123;");
            sqlcomm.Connection = sqlconn;
            sqlcomm.CommandType = System.Data.CommandType.Text;
            sqlcomm.CommandText = sqltext;
            try
            {
                sqlcomm.Connection.Open();
                id = (int)sqlcomm.ExecuteScalar() + 1;
            }
            catch (Exception)
            {

                sqlcomm.Connection.Close();
                sqlcomm.Connection.Dispose();
                throw;
            }
            sqlcomm.Connection.Close();
            sqlcomm.Connection.Dispose();
            return id;
        }

        public int GetId_drive()
        {
            int id;
            string sqltext = @"SELECT ISNULL (MAX(drive_id),0)
                                from Table_drive";
            SqlCommand sqlcomm = new SqlCommand();
            SqlConnection sqlconn = new SqlConnection("Server=MONSTERTUA;Database=Bus_pro;User Id=sa1;Password=qwerty123;");
            sqlcomm.Connection = sqlconn;
            sqlcomm.CommandType = System.Data.CommandType.Text;
            sqlcomm.CommandText = sqltext;
            try
            {
                sqlcomm.Connection.Open();
                id = (int)sqlcomm.ExecuteScalar() + 1;
            }
            catch (Exception)
            {

                sqlcomm.Connection.Close();
                sqlcomm.Connection.Dispose();
                throw;
            }
            sqlcomm.Connection.Close();
            sqlcomm.Connection.Dispose();
            return id;
        }

        public int GetId_ticket()
        {
            int id;
            string sqltext = @"SELECT ISNULL (MAX(Sale_id),10000)
                                from Table_Sale";
            SqlCommand sqlcomm = new SqlCommand();
            SqlConnection sqlconn = new SqlConnection("Server=MONSTERTUA;Database=Bus_pro;User Id=sa1;Password=qwerty123;");
            sqlcomm.Connection = sqlconn;
            sqlcomm.CommandType = System.Data.CommandType.Text;
            sqlcomm.CommandText = sqltext;
            try
            {
                sqlcomm.Connection.Open();
                id = (int)sqlcomm.ExecuteScalar() + 1;
            }
            catch (Exception)
            {

                sqlcomm.Connection.Close();
                sqlcomm.Connection.Dispose();
                throw;
            }
            sqlcomm.Connection.Close();
            sqlcomm.Connection.Dispose();
            return id;
        }

        public int GetId_seat()
        {
            int id;
            string sqltext = @"SELECT ISNULL (MAX(Seat_id),0)
                                from Table_SaleDetail";
            SqlCommand sqlcomm = new SqlCommand();
            SqlConnection sqlconn = new SqlConnection("Server=MONSTERTUA;Database=Bus_pro;User Id=sa1;Password=qwerty123;");
            sqlcomm.Connection = sqlconn;
            sqlcomm.CommandType = System.Data.CommandType.Text;
            sqlcomm.CommandText = sqltext;
            try
            {
                sqlcomm.Connection.Open();
                id = (int)sqlcomm.ExecuteScalar() + 1;
            }
            catch (Exception)
            {

                sqlcomm.Connection.Close();
                sqlcomm.Connection.Dispose();
                throw;
            }
            sqlcomm.Connection.Close();
            sqlcomm.Connection.Dispose();
            return id;
        }

        public int GetId_Promotion()
        {
            int id;
            string sqltext = @"SELECT ISNULL (MAX(Promotion_id),0)
                                from Table_Promotion";
            SqlCommand sqlcomm = new SqlCommand();
            SqlConnection sqlconn = new SqlConnection("Server=MONSTERTUA;Database=Bus_pro;User Id=sa1;Password=qwerty123;");
            sqlcomm.Connection = sqlconn;
            sqlcomm.CommandType = System.Data.CommandType.Text;
            sqlcomm.CommandText = sqltext;
            try
            {
                sqlcomm.Connection.Open();
                id = (int)sqlcomm.ExecuteScalar() + 1;
            }
            catch (Exception)
            {

                sqlcomm.Connection.Close();
                sqlcomm.Connection.Dispose();
                throw;
            }
            sqlcomm.Connection.Close();
            sqlcomm.Connection.Dispose();
            return id;
        }

        public int GetId_Promotion_round_id()
        {
            int id;
            string sqltext = @"SELECT ISNULL (MAX(Promotion_round_id),0)
                                from Table_Promotion_round";
            SqlCommand sqlcomm = new SqlCommand();
            SqlConnection sqlconn = new SqlConnection("Server=MONSTERTUA;Database=Bus_pro;User Id=sa1;Password=qwerty123;");
            sqlcomm.Connection = sqlconn;
            sqlcomm.CommandType = System.Data.CommandType.Text;
            sqlcomm.CommandText = sqltext;
            try
            {
                sqlcomm.Connection.Open();
                id = (int)sqlcomm.ExecuteScalar() + 1;
            }
            catch (Exception)
            {

                sqlcomm.Connection.Close();
                sqlcomm.Connection.Dispose();
                throw;
            }
            sqlcomm.Connection.Close();
            sqlcomm.Connection.Dispose();
            return id;
        }

        public int GetId_bus()
        {
            int id;
            string sqltext = @"SELECT ISNULL (MAX(Bus_id),0)
                                from Table_Bus";
            SqlCommand sqlcomm = new SqlCommand();
            SqlConnection sqlconn = new SqlConnection("Server=MONSTERTUA;Database=Bus_pro;User Id=sa1;Password=qwerty123;");
            sqlcomm.Connection = sqlconn;
            sqlcomm.CommandType = System.Data.CommandType.Text;
            sqlcomm.CommandText = sqltext;
            try
            {
                sqlcomm.Connection.Open();
                id = (int)sqlcomm.ExecuteScalar() + 1;
            }
            catch (Exception)
            {

                sqlcomm.Connection.Close();
                sqlcomm.Connection.Dispose();
                throw;
            }
            sqlcomm.Connection.Close();
            sqlcomm.Connection.Dispose();
            return id;
        }

        public int GetId_pay()
        {
            int id;
            string sqltext = @"SELECT ISNULL (MAX(payment_no),0)
                                from Table_payment";
            SqlCommand sqlcomm = new SqlCommand();
            SqlConnection sqlconn = new SqlConnection("Server=MONSTERTUA;Database=Bus_pro;User Id=sa1;Password=qwerty123;");
            sqlcomm.Connection = sqlconn;
            sqlcomm.CommandType = System.Data.CommandType.Text;
            sqlcomm.CommandText = sqltext;
            try
            {
                sqlcomm.Connection.Open();
                id = (int)sqlcomm.ExecuteScalar() + 1;
            }
            catch (Exception)
            {

                sqlcomm.Connection.Close();
                sqlcomm.Connection.Dispose();
                throw;
            }
            sqlcomm.Connection.Close();
            sqlcomm.Connection.Dispose();
            return id;
        }

        public int GetId_change_ticket()
        {
            int id;
            string sqltext = @"SELECT ISNULL (MAX(change_id),0)
                                from Table_changeticket";
            SqlCommand sqlcomm = new SqlCommand();
            SqlConnection sqlconn = new SqlConnection("Server=MONSTERTUA;Database=Bus_pro;User Id=sa1;Password=qwerty123;");
            sqlcomm.Connection = sqlconn;
            sqlcomm.CommandType = System.Data.CommandType.Text;
            sqlcomm.CommandText = sqltext;
            try
            {
                sqlcomm.Connection.Open();
                id = (int)sqlcomm.ExecuteScalar() + 1;
            }
            catch (Exception)
            {

                sqlcomm.Connection.Close();
                sqlcomm.Connection.Dispose();
                throw;
            }
            sqlcomm.Connection.Close();
            sqlcomm.Connection.Dispose();
            return id;
        }

        public int GetId_Around()
        {
            int id;
            string sqltext = @"SELECT ISNULL (MAX(Around_id),0)
                                from Table_Around";
            SqlCommand sqlcomm = new SqlCommand();
            SqlConnection sqlconn = new SqlConnection("Server=MONSTERTUA;Database=Bus_pro;User Id=sa1;Password=qwerty123;");
            sqlcomm.Connection = sqlconn;
            sqlcomm.CommandType = System.Data.CommandType.Text;
            sqlcomm.CommandText = sqltext;
            try
            {
                sqlcomm.Connection.Open();
                id = (int)sqlcomm.ExecuteScalar() + 1;
            }
            catch (Exception)
            {

                sqlcomm.Connection.Close();
                sqlcomm.Connection.Dispose();
                throw;
            }
            sqlcomm.Connection.Close();
            sqlcomm.Connection.Dispose();
            return id;
        }
        /// <summary>
        /// ////////////////////////////////////////////////////
        /// </summary>
        /// <returns></returns>
        public string GetId_tickettt()
        {
            string dateticket ="";
            try
            {
                int id = 0;
                string date = DateTime.Now.ToShortDateString();
                string[] stemp = date.Split('/');
                string day = stemp[0];
                string mount = stemp[1];
                string years = stemp[2].Substring(2, 2);
                if (day.Length == 1)
                {
                    day = "0" + day;
                }
                if (mount.Length == 1)
                {
                    mount = "0" + mount;
                }
                dateticket = day + mount + years;
                string sqltext = @"SELECT text = ISNULL (MAX(Sale_id),0)
                                from Table_Sale
                                WHERE Sale_id Like '" + dateticket + "%'";
                SqlCommand sqlcomm = new SqlCommand();
                SqlConnection sqlconn = new SqlConnection("Server=MONSTERTUA;Database=Bus_pro;User Id=sa1;Password=qwerty123;");
                SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
                DataTable dt = new DataTable();
                da.Fill(dt);
                if (dt.Rows.Count <= 0)
                {
                    id = 0;
                }
                else
                {
                    id = Int32.Parse(dt.Rows[0]["text"].ToString().Substring(6));
                    id++;
                }
                if (id.ToString().Length == 1)
                {
                    dateticket = dateticket + "0000" + id.ToString();
                }
                else if (id.ToString().Length == 2)
                {
                    dateticket = dateticket + "000" + id.ToString();
                }
                else if (id.ToString().Length == 3)
                {
                    dateticket = dateticket + "00" + id.ToString();
                }
                else if (id.ToString().Length == 4)
                {
                    dateticket = dateticket + "0" + id.ToString();
                }
                else if (id.ToString().Length == 5)
                {
                    dateticket = dateticket + id.ToString();
                }
                return dateticket;
            }
            catch (Exception)
            {
                dateticket = dateticket + "00000";
                return dateticket;
            }            
        }

        public int GetId_store_user()
        {
            int id;
            string sqltext = @"SELECT ISNULL (MAX(store_user_id),0)
                                from Table_store_user";
            SqlCommand sqlcomm = new SqlCommand();
            SqlConnection sqlconn = new SqlConnection("Server=MONSTERTUA;Database=Bus_pro;User Id=sa1;Password=qwerty123;");
            sqlcomm.Connection = sqlconn;
            sqlcomm.CommandType = System.Data.CommandType.Text;
            sqlcomm.CommandText = sqltext;
            try
            {
                sqlcomm.Connection.Open();
                id = (int)sqlcomm.ExecuteScalar() + 1;
            }
            catch (Exception)
            {

                sqlcomm.Connection.Close();
                sqlcomm.Connection.Dispose();
                throw;
            }
            sqlcomm.Connection.Close();
            sqlcomm.Connection.Dispose();
            return id;
        }

        public int GetId_type_bus()
        {
            int id;
            string sqltext = @"SELECT ISNULL (MAX(Type_Bus_id),0)
                                from Table_Type_Bus";
            SqlCommand sqlcomm = new SqlCommand();
            SqlConnection sqlconn = new SqlConnection("Server=MONSTERTUA;Database=Bus_pro;User Id=sa1;Password=qwerty123;");
            sqlcomm.Connection = sqlconn;
            sqlcomm.CommandType = System.Data.CommandType.Text;
            sqlcomm.CommandText = sqltext;
            try
            {
                sqlcomm.Connection.Open();
                id = (int)sqlcomm.ExecuteScalar() + 1;
            }
            catch (Exception)
            {

                sqlcomm.Connection.Close();
                sqlcomm.Connection.Dispose();
                throw;
            }
            sqlcomm.Connection.Close();
            sqlcomm.Connection.Dispose();
            return id;
        }
    }
}